package vn.com.ltv.lo.daos;

import java.util.ArrayList;

import vn.com.ltv.lo.dtos.UserDTO;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class UserDAO {
	protected static final String TABLE_NAME = "User";
	protected static final String ID = "rowid";
	protected static final String USR_ID = "usr_id";
	protected static final String USR_NM = "usr_nm";
	protected static final String USR_PWD = "usr_pwd";
	protected static final String USR_EML = "usr_eml";
	protected static final String USR_PHN = "usr_phn";
	protected static final String USR_PNT = "usr_pnt";
	protected static final String LVL_ID = "lvl_id";
	protected static String TABLE_CREATION_STRING = "create table "
			+ TABLE_NAME + " (" + ID + " integer primary key autoincrement, "
			+ USR_ID + " text, " + USR_NM + " text, " + USR_PWD + " text, "
			+ USR_EML + " text, " + USR_PHN + " text, " + USR_PNT
			+ " integer, " + LVL_ID + " integer);";
	protected static String TABLE_DROP_STRING = "drop table if exists"
			+ TABLE_NAME + ";";
	private Context context;

	public UserDAO(Context context) {
		this.context = context;
	}

	public ArrayList<UserDTO> getAll() {
		ArrayList<UserDTO> userDTOs = new ArrayList<UserDTO>();
		DatabaseHelper databaseHelper = new DatabaseHelper(this.context);
		SQLiteDatabase sQLiteDatabase = databaseHelper.getReadableDatabase();
		Cursor cursor = sQLiteDatabase.query(TABLE_NAME, null, null, null,
				null, null, null);
		if (cursor.moveToFirst()) {
			while (!cursor.isAfterLast()) {
				UserDTO userDTO = this.cursorToUserDTO(cursor);
				userDTOs.add(userDTO);
				cursor.moveToNext();
			}
		}
		cursor.close();
		sQLiteDatabase.close();
		databaseHelper.close();
		return userDTOs;
	}

	public UserDTO get(int id) {
		UserDTO userDTO = null;
		DatabaseHelper databaseHelper = new DatabaseHelper(this.context);
		SQLiteDatabase sQLiteDatabase = databaseHelper.getReadableDatabase();
		Cursor cursor = sQLiteDatabase.query(TABLE_NAME, null, ID + "=?",
				new String[] { String.valueOf(id) }, null, null, null);
		if (cursor.moveToFirst()) {
			userDTO = this.cursorToUserDTO(cursor);
		}
		cursor.close();
		sQLiteDatabase.close();
		databaseHelper.close();
		return userDTO;
	}
	
	public UserDTO get(String email) {
		
		UserDTO userDTO = null;
		DatabaseHelper databaseHelper = new DatabaseHelper(this.context);
		SQLiteDatabase sQLiteDatabase = databaseHelper.getReadableDatabase();
		Cursor cursor = sQLiteDatabase.query(TABLE_NAME, null, USR_EML + "=?",
				new String[] { String.valueOf(email) }, null, null, null);
		if (cursor.moveToFirst()) {
			userDTO = this.cursorToUserDTO(cursor);
		}
		cursor.close();
		sQLiteDatabase.close();
		databaseHelper.close();
		return userDTO;
	}
	
	public UserDTO getByUserId(String userId) {
		UserDTO userDTO = null;
		DatabaseHelper databaseHelper = new DatabaseHelper(this.context);
		SQLiteDatabase sQLiteDatabase = databaseHelper.getReadableDatabase();
		Cursor cursor = sQLiteDatabase.query(TABLE_NAME, null, USR_ID + "=?",
				new String[] { String.valueOf(userId) }, null, null, null);
		if (cursor.moveToFirst()) {
			userDTO = this.cursorToUserDTO(cursor);
		}
		cursor.close();
		sQLiteDatabase.close();
		databaseHelper.close();
		return userDTO;
	}

	public void insert(UserDTO userDTO) {
		DatabaseHelper databaseHelper = new DatabaseHelper(this.context);
		SQLiteDatabase sQLiteDatabase = databaseHelper.getWritableDatabase();
		ContentValues contentValues = this.userDTOToContentValues(userDTO);
		sQLiteDatabase.insert(TABLE_NAME, null, contentValues);
		sQLiteDatabase.close();
		databaseHelper.close();
	}

	/** * @return the number of rows affected */
	public int update(UserDTO userDTO) {
		DatabaseHelper databaseHelper = new DatabaseHelper(this.context);
		SQLiteDatabase sQLiteDatabase = databaseHelper.getWritableDatabase();
		int id = userDTO.getId();
		userDTO.setId(-1);
		ContentValues contentValues = this.userDTOToContentValues(userDTO);
		int rowCount = sQLiteDatabase.update(TABLE_NAME, contentValues, ID
				+ "=?", new String[] { String.valueOf(id) });
		sQLiteDatabase.close();
		databaseHelper.close();
		return rowCount;
	}

	public void delete(int id) {
		DatabaseHelper databaseHelper = new DatabaseHelper(this.context);
		SQLiteDatabase sQLiteDatabase = databaseHelper.getWritableDatabase();
		sQLiteDatabase.delete(TABLE_NAME, ID + "=?",
				new String[] { String.valueOf(id) });
		sQLiteDatabase.close();
		databaseHelper.close();
	}

	private UserDTO cursorToUserDTO(Cursor cursor) {
		UserDTO userDTO = new UserDTO();
		int id = cursor.getInt(cursor.getColumnIndex(ID));
		userDTO.setId(id);
		String usrId = cursor.getString(cursor.getColumnIndex(USR_ID));
		userDTO.setUsrId(usrId);
		String usrNm = cursor.getString(cursor.getColumnIndex(USR_NM));
		userDTO.setUsrNm(usrNm);
		String usrPwd = cursor.getString(cursor.getColumnIndex(USR_PWD));
		userDTO.setUsrPwd(usrPwd);
		String usrEml = cursor.getString(cursor.getColumnIndex(USR_EML));
		userDTO.setUsrEml(usrEml);
		String usrPhn = cursor.getString(cursor.getColumnIndex(USR_PHN));
		userDTO.setUsrPhn(usrPhn);
		int usrPnt = cursor.getInt(cursor.getColumnIndex(USR_PNT));
		userDTO.setUsrPnt(usrPnt);
		int lvlId = cursor.getInt(cursor.getColumnIndex(LVL_ID));
		userDTO.setLvlId(lvlId);
		return userDTO;
	}

	private ContentValues userDTOToContentValues(UserDTO userDTO) {
		ContentValues contentValues = new ContentValues();
		int id = userDTO.getId();
		if (id >= 0) {
			contentValues.put(ID, id);
		}
		String usrId = userDTO.getUsrId();
		if (usrId != null) {
			contentValues.put(USR_ID, usrId);
		}
		String usrNm = userDTO.getUsrNm();
		if (usrNm != null) {
			contentValues.put(USR_NM, usrNm);
		}
		String usrPwd = userDTO.getUsrPwd();
		if (usrPwd != null) {
			contentValues.put(USR_PWD, usrPwd);
		}
		String usrEml = userDTO.getUsrEml();
		if (usrEml != null) {
			contentValues.put(USR_EML, usrEml);
		}
		String usrPhn = userDTO.getUsrPhn();
		if (usrPhn != null) {
			contentValues.put(USR_PHN, usrPhn);
		}
		int usrPnt = userDTO.getUsrPnt();
		if (usrPnt >= 0) {
			contentValues.put(USR_PNT, usrPnt);
		}
		int lvlId = userDTO.getLvlId();
		if (lvlId >= 0) {
			contentValues.put(LVL_ID, lvlId);
		}
		return contentValues;
	}
}